# -*- coding: utf-8 -*-
from db_conn import *
import io
import time
from datetime import datetime, timedelta
import traceback
import re
from anatel_utils import *
from MySQLdb import OperationalError

date_now = datetime.now()
in_file_name_1 = "" + date_now.strftime("%Y-%m-%d") + "_file1.txt"
in_file_name_2 = "" + date_now.strftime("%Y-%m-%d") + "_file2.txt"
export_file_id = "" + date_now.strftime("%Y-%m-%d") + "-export-db-id.txt"
export_file = "" + date_now.strftime("%Y-%m-%d") + "-export-db.txt"

style_css="""
<style type="text/css">
#table-3{
    border: 1px solid #DFDFDF;background-color: #F9F9F9;width: 100%;-moz-border-radius: 3px;-webkit-border-radius: 3px;border-radius: 3px;font-family: Arial,"Bitstream Vera Sans",Helvetica,Verdana,sans-serif;color: #333;}
#table-3 td, #table-3 th {
    border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555;}
#table-3 th {
    text-shadow: rgba(255, 255, 255, 0.796875) 0px 1px 0px;font-family: Georgia,"Times New Roman","Bitstream Charter",Times,serif;font-weight: normal;padding: 7px 7px 8px;text-align: left;line-height: 1.3em;font-size: 14px;}
#table-3 td {
    font-size: 12px;padding: 4px 7px 2px;vertical-align: top;
}</style>
"""

def getAntenaId(line_txt,search_table='anatel_import'):
    try:
        cursor,conn = db_connect()
        count_ln=0
        
        #field_names = {}
        #for i in range(len(cursor.description)):
        #    field_names[cursor.description[i][0]]=i-1
        #print field_names     
        field_names = ['TransmissaoInicial',
                         'UnidadeFrequenciaTX',
                         'RecepcaoInicial',
                         'UnidadeFrequenciaRX',
                         'Situacao',
                         'CaraterSecundario',
                         'IndConfidencial',
                         'TipoClasseEstacao',
                         'Horarios',
                         'DesignacaoEmissao',
                         'PotenciaOperacao',
                         'UnidadePotencia',
                         'Equipamento',
                         'TipoAntena',
                         'EquipamentoAntena',
                         'Ganho',
                         'FrenteCosta',
                         'AnguloMeiaPotencia',
                         'AnguloElevacao',
                         'Azimute',
                         'VariacaoAzimutal',
                         'TipoPolarizacao',
                         'Altura',
                         'Raio',
                         'Latitude',
                         'Longitude',
                         'Altitude',
                         'Estacao',
                         'Servico',
                         'Indicativo',
                         'MedPotenciaMaxima_TR',
                         'MedLarguraBanda',
                         'DiametroSGCH',
                         'Diagrama',
                         'SiglaEstacao_TRS',
                         'LongitudeOrbitalSubida',
                         'NomeFeixeSubida',
                         'SiglaEstacaoDescida',
                         'LongitudeOrbitalDescida',
                         'NomeFeixeDescida',
                         'TipoEstacao',
                         'NomeEntidade',
                         'NumEntidade',
                         'NomeMunicipio',
                         'CodMunicipio',
                         'Endereco',
                         'UF',
                         'TipoClasseCorrespondencia',
                         'Rede',
                         'Distancia',]
        dict_fields={}        
        for f in range(len(field_names)):
            dict_fields[field_names[f]]=f

        line = line_txt 
                
        #start_parcial_select = time.time()
        
        select_values = ""
        line_fix = line.replace("\r","").replace("\n","").split("|")
        for col in range(len(line_fix)):
            #insert_values += """'%s',""" % (re.escape(col))
            #insert_values += """'%s',""" % (str(MySQLdb.escape_string(col)))
            #select_values += """%s = '%s' and """ % (field_names[col],str(MySQLdb.escape_string(line_fix[col])))
            if len(str(line_fix[col]).strip()) > 0:
                select_values += """%s = '%s' and """ % (field_names[col],str(MySQLdb.escape_string(line_fix[col])))
        
        select_anatel="""
        SELECT id_antena from %s
        where 
        %s
        1 = 1
        """ % (search_table,select_values)
        #print select_anatel
        try:
            #print select_anatel
            cursor.execute(select_anatel)
            resp = cursor.fetchall()
            if len(resp)>0:
                for r in resp:
                    return str(r[0])
            else:
                return "0"
        except MySQLdb.Warning, e:
            # handle warnings, if the cursor you're using raises them
            arr_campo = re.findall("'([^']*)'", str(e));
            print arr_campo
            print line.split("|")[int(dict_fields[arr_campo[0].replace("'","")])]    
        except:
            print "Erro ao atualizar o banco"
            
        #print "(Tempo Gasto Select: %s)" % ((time.time() - start_parcial_select))
        
        #start_parcial_linux = time.time()
        
        #print getDbIdFunc(line.replace("\r","").replace("\n",""))
        
        #print "(Tempo Gasto Linux: %s)" % ((time.time() - start_parcial_linux))
        
        count_ln+=1
        if count_ln%1000==0:
            print "Counter: %s" % (count_ln)
        db_disconnect(cursor,conn)
    except:
        print "Erro ao atualizar o banco"
        
def execServerLit(command):
    import os
    #print command
    execute_command = os.popen(command,"r")
    return execute_command.read()

    
def getLineByNumber(line_nbr,file_name):
    linesByNbr=execServerLit("sed -n '%sp' %s" % (line_nbr,file_name))
    #print "linesByNbr: %s" % linesByNbr
    return linesByNbr


    
def getMatchedLine(s1,file_name):
    #print 'grep -n -F "%s" %s' % (s1,file_name)
    resposta=execServerLit('grep -n -F "%s" %s' % (s1,file_name)) 
    #print "resposta: %s" % (resposta)
    try:
        return str(resposta).split(":")[0]
    except:
        return 0    

    
def getDbIdFunc(line):   
    try:
        func = getLineByNumber(getMatchedLine(line,export_file),export_file_id)
        #print "func: %s" % func
        return func.split("|")[-1]
    except:
        return "0"    
    
    
def importFile(filename):
    cursor,conn = db_connect()
    try:
        file_id = open(filename, "rb")
        table_hearders = 'TransmissaoInicial|UnidadeFrequenciaTX|RecepcaoInicial|UnidadeFrequenciaRX|Situacao|CaraterSecundario|IndConfidencial|TipoClasseEstacao|Horarios|DesignacaoEmissao|PotenciaOperacao|UnidadePotencia|Equipamento|TipoAntena|EquipamentoAntena|Ganho|FrenteCosta|AnguloMeiaPotencia|AnguloElevacao|Azimute|VariacaoAzimutal|TipoPolarizacao|Altura|Raio|Latitude|Longitude|Altitude|Estacao|Servico|Indicativo|MedPotenciaMaxima_TR|MedLarguraBanda|DiametroSGCH|Diagrama|SiglaEstacao_TRS|LongitudeOrbitalSubida|NomeFeixeSubida|SiglaEstacaoDescida|LongitudeOrbitalDescida|NomeFeixeDescida|TipoEstacao|NomeEntidade|NumEntidade|NomeMunicipio|CodMunicipio|Endereco|UF|TipoClasseCorrespondencia|Rede|Distancia'
        table_hearders_html = "<thead><tr>"
        for table_head_row in table_hearders.split("|"):
            table_hearders_html += '<th style="border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555; font-family: Georgia;font-weight: normal;padding: 7px 7px 8px;text-align: left;line-height: 1.3em;font-size: 14px;">%s</th>' % (table_head_row)
        table_hearders_html += "</tr></thead>"  
        
        file_html = open(date_now.strftime("%Y-%m-%d")+"_insert.html", "a")
        table_html="""
        <html>
        <body>
        <center>
        <h2>%s</h2>
        <h3>Data: %s</h3><br>
        <table id="table-3" style="border: 1px solid #DFDFDF;background-color: #F9F9F9; -moz-border-radius: 3px; -webkit-border-radius: 3px;border-radius: 3px; font-family: Arial;color: #333;">
        %s
        """ % ("Cadastros novos inseridos no banco",date_now.strftime("%Y-%m-%d"),table_hearders_html)
        file_html.write(table_html)
        table_html="" #freeing variable
        #table_body_html = "<tbody>"
        file_html.write("<tbody>")
        for line in file_id.readlines():
            line = line.strip().replace("\r","").replace("\n","")
            
            insert_values = ""
            #table_body_html += "<tr>"
            file_html.write("<tr>")
            for col in line.split("|"):
                #insert_values += """'%s',""" % (str(MySQLdb.escape_string(col)))
                if len(str(col))>0:
                    insert_values += ",'%s'" % (str(MySQLdb.escape_string(col)))
                else:
                    insert_values += ',NULL'                
                #table_body_html += '<td style="font-family: Georgia;font-size: 12px;padding: 4px 7px 2px;vertical-align: top;border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555;">%s</td>' % (re.escape(col))
                file_html.write('<td style="font-family: Georgia;font-size: 12px;padding: 4px 7px 2px;vertical-align: top;border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555;">%s</td>' % (col))
            file_html.write("</tr>")
            #table_body_html += "</tr>" 
            
            
            insert_anatel="""
            insert into anatel_import
            values(NULL%s,'%s',NULL);
            """ % (insert_values,filename)
            
            print insert_anatel
            
            try:
                cursor.execute(insert_anatel)
            except OperationalError:
                #muito provavelmente endereço zuado, junta e boa
                line_arr = line.split("|")
                line_arr[45]+=line_arr[45]+line_arr[46]
                del line_arr[46]
                #insert_query = "INSERT into anatel_import VALUES(" 
                #insert_query += "NULL,'" + str("','".join(map(MySQLdb.escape_string, map(str, line_arr))))
                #insert_query += "','"+filename+"',NULL);"
                
                insert_query = "INSERT into anatel_import VALUES(" 
                #insert_query += str(row[0]) + ",'" + str("','".join(map(MySQLdb.escape_string, map(str, row[1:]))))
                
                insert_query += "NULL,'"
                for r in line_arr:
                    if len(str(r))>0:
                        insert_query += ",'%s'" % (str(MySQLdb.escape_string(r)))
                    else:
                        insert_query += ',NULL'
                        
                    #if not r == row[-1]:
                    #    insert_query += ''

                insert_query += ",'"+filename+"',NULL);"
                                
                cursor.execute(insert_query)
                #print "Entrou em execao, conserta:"
                #print insert_query
                
            updateTable(cursor.lastrowid,1)
            
        #table_body_html += "</tbody>" 
        file_html.write("</tbody>")      
        file_html.write("""
        </table>
        </center>
        </body>
        </html>
        """ )
        #file_id.write(table_html)
        file_id.close()
        file_html.close()
        #sendMail(["wchaves@gmail.com","fernandolaguardia@gmail.com"], "Relatorio Cadastros Novos Anatel %s" % date_now.strftime("%Y-%m-%d"), table_html)
        sendMail(["wchaves@gmail.com"], "Relatorio Cadastros Novos Anatel %s" % date_now.strftime("%Y-%m-%d"), table_html)
                    

    except:        
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        print exceptionType
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)
    finally:
        db_disconnect(cursor,conn)

def deleteDb(filename): 
    cursor,conn = db_connect()   
    try:
        file_id = open(filename, "rb")
        
        table_hearders = 'Id_Antena|TransmissaoInicial|UnidadeFrequenciaTX|RecepcaoInicial|UnidadeFrequenciaRX|Situacao|CaraterSecundario|IndConfidencial|TipoClasseEstacao|Horarios|DesignacaoEmissao|PotenciaOperacao|UnidadePotencia|Equipamento|TipoAntena|EquipamentoAntena|Ganho|FrenteCosta|AnguloMeiaPotencia|AnguloElevacao|Azimute|VariacaoAzimutal|TipoPolarizacao|Altura|Raio|Latitude|Longitude|Altitude|Estacao|Servico|Indicativo|MedPotenciaMaxima_TR|MedLarguraBanda|DiametroSGCH|Diagrama|SiglaEstacao_TRS|LongitudeOrbitalSubida|NomeFeixeSubida|SiglaEstacaoDescida|LongitudeOrbitalDescida|NomeFeixeDescida|TipoEstacao|NomeEntidade|NumEntidade|NomeMunicipio|CodMunicipio|Endereco|UF|TipoClasseCorrespondencia|Rede|Distancia|NomeBase|Atualizacao'
        table_hearders_html = "<thead><tr>"
        for table_head_row in table_hearders.split("|"):
            table_hearders_html += '<th style="border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555; font-family: Georgia;font-weight: normal;padding: 7px 7px 8px;text-align: left;line-height: 1.3em;font-size: 14px;">%s</th>' % (table_head_row)
        table_hearders_html += "</tr></thead>"
        
        table_body_html = "<tbody>"
        for line in file_id.readlines():
            #print line
            id_antena_del = getAntenaId(line.strip().replace("\r","").replace("\n",""))
            
            cursor.execute("select * from anatel_import where id_antena = %s" % (id_antena_del))
            resp = cursor.fetchall()
            
            for row in resp:
                table_body_html += "<tr>"
                for col in row:
                    table_body_html += '<td style="font-family: Georgia;font-size: 12px;padding: 4px 7px 2px;vertical-align: top;border-top-color: white;border-bottom: 1px solid #DFDFDF;color: #555;">%s</td>' % (col)
                table_body_html += "</tr>"
            
            if id_antena_del:
                del_query = "DELETE FROM anatel_import WHERE id_antena = %s" % (id_antena_del)
                #print del_query
                cursor.execute(del_query)
                updateTable(id_antena_del,3)
            else:
                raise
            
        table_body_html += "</tbody>"
        
        table_html="""
        <html>
        <body>
        <center>
        <h2>%s</h2>
        <h3>Data: %s</h3><br>
        <table id="table-3" style="border: 1px solid #DFDFDF;background-color: #F9F9F9; -moz-border-radius: 3px; -webkit-border-radius: 3px;border-radius: 3px; font-family: Arial;color: #333;">
        %s
        %s
        </table>
        </center>
        </body>
        </html>
        """ % ("Cadastros apagados do banco",date_now.strftime("%Y-%m-%d"),table_hearders_html,table_body_html)
        
        file_id.close()
        
        file_html = open(date_now.strftime("%Y-%m-%d")+"_delete.html", "a")
        file_html.write(table_html)
        file_html.close()
        
        #freeing memory :)
        table_hearders_html=""
        table_body_html=""        
        #sendMail(["wchaves@gmail.com","fernandolaguardia@gmail.com"], "Relatorio Cadastros Anatel Apagados %s" % date_now.strftime("%Y-%m-%d"), table_html)
        sendMail(["wchaves@gmail.com"], "Relatorio Cadastros Anatel Apagados %s" % date_now.strftime("%Y-%m-%d"), table_html)
        
    except:        
        exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
        #print exceptionType
        traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                          limit=2, file=sys.stdout)
    finally:
        db_disconnect(cursor,conn)

if __name__ == '__main__':
    if not len(sys.argv) < 2:
        
        for v in sys.argv:
            if len(v.split("="))>1:
                param = v.split("=")[0].strip()
                valor = v.split("=")[1].strip()
                try:
                    if param.lower().strip().find("days_offset")>-1:
                        days_offset = valor
                        print "days_offset: %s" % (days_offset)
                        date_now = datetime.now() - timedelta(days=(int(days_offset)))
                        in_file_name_1 = "" + date_now.strftime("%Y-%m-%d") + "_file1.txt"
                        in_file_name_2 = "" + date_now.strftime("%Y-%m-%d") + "_file2.txt"
                        export_file_id = "" + date_now.strftime("%Y-%m-%d") + "-export-db-id.txt"
                        export_file = "" + date_now.strftime("%Y-%m-%d") + "-export-db.txt"
                    elif param.lower().strip().find("date_custom")>-1:
                        date_custom = valor
                        print "date_custom: %s" % (date_custom)
                        date_now = datetime.strptime(date_custom, '%Y-%m-%d')
                        in_file_name_1 = "" + date_custom + "_file1.txt"
                        in_file_name_2 = "" + date_custom + "_file2.txt"
                        export_file_id = "" + date_custom + "-export-db-id.txt"
                        export_file = "" + date_custom + "-export-db.txt"                                             
                    else:
                        eval('%s=%s' % (param,valor))   
                    #print "Param: %s" % (param)
                    #print "Valor: %s" % (valor)
                except:
                    exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
                    traceback.print_exception(exceptionType, exceptionValue, exceptionTraceback,
                                      limit=2, file=sys.stdout)      
    start_total = time.time()
    
    start = time.time() 
    deleteDb(in_file_name_2)
    print "(Delete finalizado: %s)" % ((time.time() - start))
        
    start = time.time() 
    importFile(in_file_name_1)
    print "(Import Finalizado: %s)" % ((time.time() - start))


    print "(Total Tempo Gasto: %s)" % ((time.time() - start_total))